﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.SqlClient;

namespace WebQueryRole
{
    public partial class sqloutput : System.Web.UI.Page
    {

        private DBCollection _dbCollection = new DBCollection();
        protected void Page_Load(object sender, EventArgs e)
        {
            string outType = Request["output_type"];
            string sql = Request["sql_code"];

            if (outType == null)
            {
                return;
            }

            if (outType.Equals("csv"))
            {
                PrintCSV(sql);
            }
        }

        protected override void OnLoadComplete(EventArgs e)
        {
            base.OnLoadComplete(e);

            _dbCollection.CloseAll();

        }

        private void PrintCSV(string sql)
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, _dbCollection["data"]);

            DataSet ds = new DataSet();
            DataTable dt = null;
            try
            {
                da.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception e)
            {
                errorDiv.InnerHtml = e.Message;
                return;
            }


            Response.Clear();
            Response.ContentType = "APPLICATION/OCTET-STREAM";
            Response.AddHeader("Content-Disposition", "attachment;filename=\"output.tab\"");
            Response.AddHeader("content-type", "text/csv");

            using (StreamWriter writer = new StreamWriter(Response.OutputStream))
            {

                int iColCount = dt.Columns.Count;
                for (int i = 0; i < iColCount; i++)
                {
                    writer.Write(dt.Columns[i]);
                    if (i < iColCount - 1)
                    {
                        writer.Write("\t");
                    }
                }
                writer.Write(writer.NewLine);

                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < iColCount; i++)
                    {
                        if (!Convert.IsDBNull(dr[i]))
                        {
                            writer.Write(dr[i].ToString());
                        }
                        if (i < iColCount - 1)
                        {
                            writer.Write("\t");
                        }
                    }

                    writer.Write(writer.NewLine);
                }

                writer.Close();
            }            

            Response.End();
        }      
    }
}
